package com.popcorn.comment.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.popcorn.comment.model.commentModel;
import com.popcorn.notice.model.NoticeModel;

public class commentDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	DataSource ds = null;
	ResultSet rs = null;
	
	public commentDAO(){
		try{
			Context init = new InitialContext();
			ds = (DataSource)init.lookup("java:comp/env/jdbc/Mysql");
			         
		}catch(Exception e){
		e.printStackTrace();
		}	  
	}
	
    public boolean insert(commentModel vo){
    	int result = 0;
		int num =0;
		int cnt = 0;
		String sql="";			
		
		try{
			try {
				conn = ds.getConnection();				
			} catch (Exception e) {
				System.out.println("connect Exception : "+e.toString());
			}
			pstmt=conn.prepareStatement("select max(COM_SEQ) from comment");
			rs = pstmt.executeQuery();
			
			if(rs.next())
				num =rs.getInt(1)+1;
			else
				num=1;
			 	sql="insert into comment values(?,?,?,now(),?)";
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setInt(1, num);
	            pstmt.setString(2, vo.getNICKNAME());
	            pstmt.setString(3, vo.getCONTENT());
	            pstmt.setString(4, vo.getSEQ_BBS());
	            result=pstmt.executeUpdate();
	            
	            pstmt=conn.prepareStatement("select count(*) from comment where SEQ_BBS='"+vo.getSEQ_BBS()+"'");
				rs = pstmt.executeQuery();
	            if(rs.next()){
	            	cnt = rs.getInt(1);
	            	String [] div = vo.getSEQ_BBS().split("_");
	            	sql="update "+div[0]+" set COM_CNT=? where SEQ_BBS="+div[1];
	            	System.out.println(sql);
	            	pstmt=conn.prepareStatement(sql);
		            pstmt.setInt(1, cnt);
		            result=pstmt.executeUpdate();
	            }
			if(result==0)return false;
			
			return true;
			
		}catch(Exception ex){
			System.out.println("등록 실패");
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return false;
    }
    
    public List getList(String command){
    	String sql="";			
		List list = new ArrayList();			
		try{
			try {
				conn = ds.getConnection();				
			} catch (Exception e) {
				System.out.println("connect Exception : "+e.toString());
			}
			 
			sql="select * from comment where SEQ_BBS=? order by COM_SEQ asc";				
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, command);
			rs = pstmt.executeQuery();
			while(rs.next()){
				commentModel model = new commentModel();
				model.setCOM_DTM(rs.getString("COM_DTM"));
				model.setCONTENT(rs.getString("CONTENT"));
				model.setNICKNAME(rs.getString("NICKNAME"));
				model.setCOM_SEQ(rs.getInt("COM_SEQ"));
				list.add(model);
			}				
			return list;
			
		}catch(Exception ex){
			System.out.println("보기 실패");
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return null;
    }

	public boolean delete(String idx, String NICKNAME) {
    	int result = 0;
		int num =0;
		String sql="";			
		int cnt = 0;
		try{
			try {
				conn = ds.getConnection();				
			} catch (Exception e) {
				System.out.println("connect Exception : "+e.toString());
			}
            
			sql="select * from comment where COM_SEQ='"+idx+"'";
            pstmt=conn.prepareStatement(sql);
            System.out.println(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				String seq = rs.getString("SEQ_BBS");
				sql="select count(*) from comment where SEQ_BBS='"+seq+"'";
				System.out.println(sql);
	            pstmt=conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
	            if(rs.next()){
	            	cnt = rs.getInt(1);
	            	String [] div = seq.split("_");
	            	sql="update "+div[0]+" set COM_CNT=? where SEQ_BBS="+div[1];
	            	System.out.println(sql);
	            	pstmt=conn.prepareStatement(sql);
		            pstmt.setInt(1, cnt-1);
		            result=pstmt.executeUpdate();
	            }
			}

			sql="delete from comment where COM_SEQ="+idx+" and NICKNAME='"+NICKNAME+"'";
            pstmt=conn.prepareStatement(sql);
            result=pstmt.executeUpdate();
            System.out.println(sql);

			if(result==0)return false;
			
			return true;
			
		}catch(Exception ex){
			System.out.println("삭제 실패");
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return false;
	}
}